Vlookup函数的使用方法(入门+进阶+高级+最新用法) |
您所在的位置:网站首页 › vlook up函数 视频 › Vlookup函数的使用方法(入门+进阶+高级+最新用法) |
=Vlookup( 查找的值, 查找区域或数组, 返回值所在的列数, 精确or匹配查找) 语法说明: 查找的值 :要查找的值 查找区域或数组 :包含查找值字段和返回值的单元格区域或数组 返回值的在列数 :返回值在查找区域的列数 精确or匹配查找 :值为0或False为精确查找,值为1或true时匹配查找。二、入门篇 1、单条件查找 【例1】根据姓名查找基本工资 =VLOOKUP(G2,B:E,4,0) 注: G2 :是要查找的值 B:E :是查找区域。因为要查找的姓名在第2列,所以区域也要从B列开始。 4 :是基本工资在B:E区域中的第4列 0 :是精确查找2、查找不到时返回空 【例1】根据姓名查找基本工资 =IFERROR(VLOOKUP(G2,B:E,4,0),"") 注:IFERROR函数可以把错误值转换为指定的值,本例公式中转换为空 三、进阶篇 1、反向查找 【例】根据姓名查部门 =VLOOKUP(G2,IF({1,0},B1:B8,A1:A8),2,0) 注:公式中用IF({1,0} 把B列和A列组合在一起,并把 B列放在A列前面。 2、包含查找 【例】查找含“一”的姓名对应的基本工资 =VLOOKUP("*"&G2&"*",B:E,4,0) 注:查找值两边连接通配符*号可以实现包含查找 3、区间查找 【例】根据销量查找对应区间的提成 =VLOOKUP(D2,A:B,2,1) 注:当最后一个参数为1或省略时,可以实现匹配或区间查找。规则是查找比被查找值小且最接近的值,并返回对应N列(第3个参数)的结果。如下图所示查找180,在A列查找比180且最接近的值是100,返回100对应的提成3%。 4、含通配符的查找 【例】型号查找单价 错误公式: =VLOOKUP(D2,A:B,2,0) 正确公式: =VLOOKUP(SUBSTITUTE(D2,"*","~*"),A:B,2,0) 注:把*用函数替换为~*后就可以正常查找了 5、横向多列查找 【例】根据姓名查找性别、年龄和基本工资 注:用Column函数生成动态数字,作为Vlookup第3个参数,一个公式向右复制即可查找全部 6、多区域查找 【例9】根据不同的表从不同的区域查询 =VLOOKUP(B2,IF(A2="销售一部",A5:B9,D5:E9),2,0) 四、高级篇 1、多条件查找 【例】根据部门和姓名查工资 =VLOOKUP(E2&F2,IF({1,0},A2:A8&B2:B8,C2:C8),2,0) 注:先把A列和B列连接在一起,再用IF({1,0} 把它和C列组合在一起构成8行2列的数组,作为Vlookup的第2个参数 2、合并单元格查找 【例】查找所在部门的奖金 3、合并单元格查找 【例】根据公司、产品查找对应价格 注:用Match查找出部门所在行数,然后用offset函数向下偏移B1,进尔和C99构成一个动态的区域。更简单的说就是部门在哪一行,我就用Vlookup从哪一行开始向下找。 4、一对多查找 【例】查找出人事部所有员工 数组公式输入完成后按Ctrl+shift+enter结束后自动添加大括号 注: INDIRECT("a2:a"& row : 生成行数逐渐增多的7个区域 COUNTIF( INDIRECT : 在7个区域中分别计算部门的个数,相当于给人事部生成编号 IF({1,0} : 把带编号的部门和B列构成7行两列的新数组5、查找所有值放在一个单元格 【例】在G列设置公式,根据F列产品从左表中查找所有符合条件的价格并用逗号隔开。 公式: G2=VLOOKUP(F2,C:E,3,)'6、查找最后一个 【例】查找A产品最后一次进货价格 =VLOOKUP(1,IF({100,0},0/(B2:B10="A"),C2:C10),2) 注:Vlookup最后一个参数省略时,可以象lookup进行二分法查找,用0/(条件)把不符合条件的变成错误值,符合条件的变成0,然后用一个足够大的数查找。IF后兰色故意把常见的1写成100,想让大家知道这个只要是非0的数字都可以。 7、跨多表查找 【例】从各部门中查找员工的基本工资,在哪一个表中不一定。 方法1 =IFERROR( VLOOKUP(A2,服务!A:G,7,0),IFERROR( VLOOKUP(A2,人事!A:G,7,0),IFERROR( VLOOKUP(A2,综合!A:G,7,0),IFERROR( VLOOKUP(A2,财务!A:G,7,0),IFERROR( VLOOKUP(A2,销售!A:G,7,0),"无此人信息"))))) 方法2: =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0) 五、office365中的新用法 1、批量查找 在最新的office365版本,查找再多行只需要设置一个公式的 E2单元格 =Vlookup(d2:d12,A:B,2,0) 2、多列查找 多查查找也可以只设置一个公式 =VLOOKUP(A11,A1:E7,{2,3,5},0) 来源:Excel精英培训;作者:兰色幻想-赵志东返回搜狐,查看更多 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |